package com.wosaitech.baby.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.wosaitech.baby.entry.Score;
import com.wosaitech.baby.entry.WXUser;
import com.wosaitech.baby.pojo.ScoreResult;
import com.wosaitech.baby.utils.DateUtils;
import com.wosaitech.baby.utils.GodUtils;

@Repository
public class DataDao extends BaseDAO {
	public WXUser getWXUserByOpenId(String openId) {
		Map<String, String> objMap = new HashMap<String, String>();
		StringBuffer hql = new StringBuffer("select u from WXUser u");
		hql.append(" where 1 = 1");
		hql.append(" and u.weixin_openid = ?0");
		objMap.put("0", openId);

		List<?> pList = this.query(hql.toString(), objMap);
		if (GodUtils.CheckNull(pList)) {
			return null;
		}
		return (WXUser) pList.get(0);
	}

	public List<Score> findScore(String month, String score) {
		Map<String, String> objMap = new HashMap<String, String>();
		StringBuffer hql = new StringBuffer("select u from WXUser u");
		hql.append(" where 1 = 1");
		hql.append(" and u.weixin_openid = ?0");

		List<Score> pList = (List<Score>) this.query(hql.toString(), objMap);
		return pList;
	}

	public boolean hasScoreByOpenIdAndShareOpenId(String openId,
			String shareOpenId) {
		Map<String, Object> objMap = new HashMap<String, Object>();
		StringBuffer hql = new StringBuffer("from Score s");
		hql.append(" where 1 = 1");
		hql.append(" and s.openId = ?0");
		hql.append(" and s.shareOpenId = ?1");
		hql.append(" and s.createdate >= ?2");
		hql.append(" and s.createdate <= ?3");
		objMap.put("0", openId);
		objMap.put("1", shareOpenId);
		objMap.put("2", DateUtils.getMonthStart());
		objMap.put("3", DateUtils.getMonthEnd());
		List<?> pList = this.query(hql.toString(), objMap);
		if (GodUtils.CheckNull(pList)) {
			return false;
		} else {
			return true;
		}
	}

	public int queryScoreByOpenId(String openId) {
		Map<String, Object> objMap = new HashMap<String, Object>();
		StringBuffer hql = new StringBuffer("select sum(s.point) from Score s");
		hql.append(" where 1 = 1");
		hql.append(" and s.openId = ?0");
		hql.append(" and s.createdate >= ?1");
		hql.append(" and s.createdate <= ?2");
		objMap.put("0", openId);
		objMap.put("1", DateUtils.getMonthStart());
		objMap.put("2", DateUtils.getMonthEnd());
		List<?> pList = this.query(hql.toString(), objMap);
		if (GodUtils.CheckNull(pList)) {
			return 0;
		} else {
			Long p = (Long) pList.get(0);
			if (p == null) {
				return 0;
			} else {
				return p.intValue();
			}
		}

	}

	public int queryCountScore(String openId) {
		Map<String, Object> objMap = new HashMap<String, Object>();
		StringBuffer hql = new StringBuffer("from Score s");
		hql.append(" where 1 = 1");
		hql.append(" and s.openId = ?0");
		hql.append(" and s.shareOpenId is null");
		hql.append(" and s.createdate >= ?1");
		hql.append(" and s.createdate <= ?2");
		objMap.put("0", openId);
		objMap.put("1", DateUtils.getMonthStart());
		objMap.put("2", DateUtils.getMonthEnd());
		List<?> pList = this.query(hql.toString(), objMap);
		if (GodUtils.CheckNull(pList)) {
			return 0;
		} else {
			return pList.size();
		}
	}

	public List<ScoreResult> findScoreResultByDateAndScore(Date date1,
			Date date2, int score1, int score2) {
		List<ScoreResult> srList = new ArrayList<ScoreResult>();
		StringBuffer sql = new StringBuffer(
				"SELECT s.open_id, w.phone, w.address, sum(s.point) sp");
		sql.append(" FROM score s, sys_weixinuser w");
		sql.append(" WHERE s.open_id = w.weixin_openid");
		sql.append(" and s.create_date >= ?");
		sql.append(" and s.create_date <= ?");
		sql.append(" GROUP BY s.open_id");
		sql.append(" HAVING sp >= ?");
		if (score2 != 0) {
			sql.append(" and sp < ?");
		}
		sql.append(" ORDER BY sp DESC");

		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, date1);
		query.setParameter(1, date2);
		query.setParameter(2, score1);
		if (score2 != 0) {
			query.setParameter(3, score2);
		}
		Iterator<Object[]> it = query.list().iterator();
		while (it.hasNext()) {
			Object[] obj = it.next();
			String openId = (String) obj[0];
			String phone = (String) obj[1];
			String address = (String) obj[2];
			Integer point = ((BigDecimal) obj[3]).intValue();
			ScoreResult sr = new ScoreResult(openId, phone, address, point);
			srList.add(sr);
		}
		return srList;
	}
}
